package com.lmx.persistence.service;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.lmx.common.util.CommonUtil;
import com.lmx.repoDomain.tmodel.Customer;
import com.lmx.repoDomain.tmodel.Task;

/**
 * #{description}
 *
 * @author
 * @version 1.00
 */
@Service
@Scope("prototype")
public class ImportExcelService<T> {
	private Log logger = LogFactory.getLog(this.getClass());

	@Autowired
	protected GenericService dslJpaService;
	
	@PersistenceContext
    private EntityManager em;

	/**
	 * 读取出filePath中的所有数据信息，添加数据库
	 * 
	 */
	@Transactional
	public Map<String, Object> getDataFromExcel(InputStream inputStream,Long taskID) {
		Map<String, Object> msgMap = new HashMap<String, Object>();
		try {
			// 创建对Excel工作簿文件
			// HSSFWorkbook wookbook= new HSSFWorkbook(new
			// FileInputStream(filePath));
			// 在Excel文档中，第一张工作表的缺省索引是0，
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			// HSSFSheet sheet = wookbook.getSheet("Sheet1");
			Workbook book = null;
			try {
				book = new XSSFWorkbook(inputStream);
			} catch (Exception ex) {
				book = new HSSFWorkbook(inputStream);
			}

			Sheet sheet = book.getSheet("Sheet1");
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();

			List<Customer> list = new ArrayList<Customer>();
			// 遍历行
			for (int i = 1; i < rows; i++) {
				// 读取左上端单元格
				Row row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getPhysicalNumberOfCells();
					String value = "";
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						Cell cell = row.getCell(j);
						if (cell != null) {
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							
							String cellStr = cell.getStringCellValue();
							if(cellStr.equals("")){
								cellStr = "null";
							}
							switch (cell.getCellType()) {
							case Cell.CELL_TYPE_FORMULA:
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value += cell.getNumericCellValue() + ",";
								break;
							case Cell.CELL_TYPE_STRING:
								value += cellStr + ",";
								break;
							default:
								value += "0";
								break;
							}
						}
					}
					// 将数据封装到实体类中
					String[] val = value.split(",");
					Customer customer = new Customer();
					if(val != null && val.length>0){
						// 验证name
						String name = val[0];
						if (!name.isEmpty()) {
							customer.setName(name);

							// 验证联系电话
							String phone = val[1];
							if (!phone.isEmpty()) {
								if (isMobile(phone)) {// 验证是否为手机号
									customer.setPhone(phone);
								} else if (isPhone(phone)) {// 验证是否为固话
									customer.setPhone(phone);
								}else{
									customer.setPhone("");
									logger.debug(name + "联系电话格式不正确：" + phone);
								}
								
								if(!customer.getPhone().isEmpty()){
									if(val.length>2){
										if(!val[2].equals("null")){
											customer.setEmail(val[2]);
										}
									}
//									if(val.length>3){
//										if(!val[3].equals("null")){
//											customer.setEnterprise(val[3]);
//										}
//									}
//									if(val.length>4){
//										if(!val[4].equals("null")){
//											customer.setPosition(val[4]);
//										}
//									}
									if(val.length>5){
										if(!val[5].equals("null")){
											customer.setAddress(val[5]);
										}
									}
									if(val.length>6){
										if(!val[6].equals("null")){
											customer.setRemark(val[6]);
										}
									}

									// 任务状态：是否已完成,0 未完成
									short isFinish = 0;
									//customer.setIsFinish(isFinish);
									//是否已分配客服0 ：未分配    1：已分配
									customer.setIsAllocated((short) 0);
									// 所属任务
									Task task = new Task();
									task.setId(taskID);
									//customer.setTask(task);
									
									list.add(customer);
								}
							}
						}
					}
				}
			}
			int count = 0;
			List<Customer> customerList = new ArrayList<>();
			for (int i = 0; i < list.size(); i++) {
				Customer customer = list.get(i);
				//验证是否重复数据
				//根据客户姓名、电话、任务id查询数据数量 
				int num = (int)(long)this.em.createQuery("select count(c.id) from Customer c where c.name = ?1 and c.phone = ?2 and c.task.id = ?3")
						.setParameter(1, customer.getName())
						.setParameter(2, customer.getPhone())
						//.setParameter(3, customer.getTask().getId())
						.setParameter(3, null)
						.getSingleResult();
				if(num>0){//如果num大于0，该数据为重复数据
					//logger.debug("姓名："+customer.getName()+",联系电话："+customer.getPhone()+",任务id："+customer.getTask().getId()+"该数据重复");
				}else{
					// 插入数据库
					dslJpaService.setEntityClass(Customer.class);
					customer = (Customer)dslJpaService.create(customer);
					customerList.add(customer);
					count = count + 1;
					logger.debug("插入数据库成功!成功插入" + count + "条");
				}
			}
//			//获取该任务的客服列表
//			List result = this.em.createQuery("select u from UserTask ut,User u where ut.user.id = u.id and ut.task.id = ?1 and ut.status.typecode = ?2")
//					.setParameter(1, taskID)
//					.setParameter(2, "taskApplySucc")//已通过
//					.getResultList();
//			if(result.size()>0){
//				int userNum = result.size();//客服数量
//				int customerNum = customerList.size();//客户数量
//				int size = customerNum/userNum;  //每个客服绑定客户数量
//				int num =  customerNum%userNum;//余数
//				for(int i=0;i<userNum;i++){//循环客服
//					for(int j=i*size;j<(i+1)*size;j++){//循环客户
//						CsCustomer csCustomer = new CsCustomer();
//						User cs = (User) result.get(i);
//						csCustomer.setCs(cs);
//						Customer customer = customerList.get(j);
//						csCustomer.setCustomer(customer);
//						csCustomer.setCreateTime(CommonUtil.getCurrTimes());
//						csCustomer.setUpdateTime(CommonUtil.getCurrTimes());
//						csCustomer.setStatus(0);
//						csCustomer.setDelStatus((short) 0);
//						// 插入数据库
//						dslJpaService.setEntityClass(CsCustomer.class);
//						dslJpaService.create(csCustomer);
//					}
//				}
//				if(num>0){
//					User cs = (User) result.get(0);
//					for(int i=(customerNum-num);i<customerNum;i++){
//						CsCustomer csCustomer = new CsCustomer();
//						csCustomer.setCs(cs);
//						Customer customer = customerList.get(i);
//						csCustomer.setCustomer(customer);
//						csCustomer.setCreateTime(CommonUtil.getCurrTimes());
//						csCustomer.setUpdateTime(CommonUtil.getCurrTimes());
//						csCustomer.setStatus(0);
//						csCustomer.setDelStatus((short) 0);
//						// 插入数据库
//						dslJpaService.setEntityClass(CsCustomer.class);
//						dslJpaService.create(csCustomer);
//					}
//				}
//				
//			}
			if (list.size() > count) {// 如果总行数大于插入数据，部分插入
				msgMap.put("flag", "1");
				msgMap.put("message", "成功导入" + count + "条，失败"+ ((rows-1) -count) + "条,其中重复数据"+(list.size()-count)+"条");
			} else if (list.size() == count) {// 如果总行数等于插入数据，全部插入
				msgMap.put("flag", "0");
				msgMap.put("message", "全部导入成功" + count + "条");
			}

		} catch (FileNotFoundException e) {
			e.fillInStackTrace();
		} catch (IOException e) {
			e.fillInStackTrace();
		}
		return msgMap;
	}
	
	/**
	 * 读取移动业务excel模板
	 * 
	 */
	@Transactional
	public Map<String, Object> getMobileServiceFromExcel(InputStream inputStream,Long taskID) {
		Map<String, Object> msgMap = new HashMap<String, Object>();
		try {
			// 创建对Excel工作簿文件
			// HSSFWorkbook wookbook= new HSSFWorkbook(new
			// FileInputStream(filePath));
			// 在Excel文档中，第一张工作表的缺省索引是0，
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			// HSSFSheet sheet = wookbook.getSheet("Sheet1");
			Workbook book = null;
			try {
				book = new XSSFWorkbook(inputStream);
			} catch (Exception ex) {
				book = new HSSFWorkbook(inputStream);
			}

			Sheet sheet = book.getSheet("Sheet1");
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			//获取第一行
			Row rowOne = sheet.getRow(0);
			String valueTitle = "";
			if(rowOne != null){
				int cells = rowOne.getPhysicalNumberOfCells();//获取第一行的所有列
				// 遍历第一行所有列
				for (int j = 0; j < cells; j++) {
					// 获取到列的值
					Cell cell = rowOne.getCell(j);
					if (cell != null) {
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						switch (cell.getCellType()) {
						case Cell.CELL_TYPE_STRING:
							valueTitle += cell.getStringCellValue() + ",";
							break;
						default:
							valueTitle += "0";
							break;
						}
					}
				}
			}
			
			List<Customer> list = new ArrayList<Customer>();
			// 遍历行
			for (int i = 1; i < rows; i++) {
				// 读取左上端单元格
				Row row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getPhysicalNumberOfCells();
					String value = "";
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						Cell cell = row.getCell(j);
						if (cell != null) {
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							
							String cellStr = cell.getStringCellValue();
							if(cellStr.equals("")){
								cellStr = "null";
							}
							switch (cell.getCellType()) {
							case Cell.CELL_TYPE_FORMULA:
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value += cell.getNumericCellValue() + ",";
								break;
							case Cell.CELL_TYPE_STRING:
								value += cellStr + ",";
								break;
							default:
								value += "0";
								break;
							}
						}
					}
					// 将数据封装到实体类中
					String[] valTitle = valueTitle.split(",");
					String[] val = value.split(",");
					Customer customer = new Customer();
					if(val != null && val.length>0){
						// 验证联系电话
						String phone = val[1];
						if (!phone.isEmpty()) {
							if (isMobile(phone)) {// 验证是否为手机号
								customer.setPhone(phone);
							} else if (isPhone(phone)) {// 验证是否为固话
								customer.setPhone(phone);
							}else{
								customer.setPhone("");
								logger.debug("联系电话格式不正确：" + phone);
							}
							
							if(!customer.getPhone().isEmpty()){
								List<Map<String,Object>> extendInfoList = new ArrayList<Map<String,Object>>();
 								if(val.length>0){
									if(!val[0].equals("null")){
										Map<String ,Object> extendInfoMap = new HashMap<String, Object>();
										extendInfoMap.put("title", valTitle[0]);
										extendInfoMap.put("content", val[0]);
										extendInfoMap.put("sort", 1);
										
										extendInfoList.add(extendInfoMap);
									}
								}
 								for(int m=2;m<val.length;m++){
 									if(!val[m].equals("null")){
 										Map<String ,Object> extendInfoMap = new HashMap<String, Object>();
										extendInfoMap.put("title", valTitle[m]);
										extendInfoMap.put("content", val[m]);
										extendInfoMap.put("sort", m);
										
										extendInfoList.add(extendInfoMap);
									}
 								}
								Map<String ,Object> map = new HashMap<String, Object>();
								map.put("extendInfo", extendInfoList);
								String extendInfo = CommonUtil.mapToJsonString(map);
								customer.setExtendInfo(extendInfo);
								
								// 任务状态：是否已完成,0 未完成
								short isFinish = 0;
								//customer.setIsFinish(isFinish);
								//是否已分配客服0 ：未分配    1：已分配
								customer.setIsAllocated((short) 0);
								// 所属任务
								Task task = new Task();
								task.setId(taskID);
								//customer.setTask(task);
								
								list.add(customer);
							}
						}	
					}
				}
			}
			int count = 0;
			List<Customer> customerList = new ArrayList<>();
			for (int i = 0; i < list.size(); i++) {
				Customer customer = list.get(i);
				//验证是否重复数据
				//根据电话、任务id查询数据数量 
				int num = (int)(long)this.em.createQuery("select count(c.id) from Customer c where c.phone = ?2 and c.task.id = ?3")
						.setParameter(2, customer.getPhone())
						//.setParameter(3, customer.getTask().getId())
						.setParameter(3, null)
						.getSingleResult();
				if(num>0){//如果num大于0，该数据为重复数据
					//logger.debug("联系电话："+customer.getPhone()+",任务id："+customer.getTask().getId()+"该数据重复");
				}else{
					// 插入数据库
					dslJpaService.setEntityClass(Customer.class);
					customer = (Customer)dslJpaService.create(customer);
					customerList.add(customer);
					count = count + 1;
					logger.debug("插入数据库成功!成功插入" + count + "条");
				}
			}
			if (list.size() > count) {// 如果总行数大于插入数据，部分插入
				msgMap.put("flag", "1");
				msgMap.put("message", "成功导入" + count + "条，失败"+ ((rows-1) -count) + "条,其中重复数据"+(list.size()-count)+"条");
			} else if (list.size() == count) {// 如果总行数等于插入数据，全部插入
				msgMap.put("flag", "0");
				msgMap.put("message", "全部导入成功" + count + "条");
			}

		} catch (FileNotFoundException e) {
			e.fillInStackTrace();
		} catch (IOException e) {
			e.fillInStackTrace();
		}
		return msgMap;
	}

	/**
	 * 手机号验证
	 * 
	 * @param str
	 * @return 验证通过返回true
	 */
	public static boolean isMobile(String str) {
		String regex = "^((13[0-9])|(17[0-1,6-8])|(15[^4,\\\\D])|(18[0-9]))\\d{8}$";    
        return Pattern.matches(regex, str);   
	}

	/**
	 * 电话号码验证
	 * 
	 * @param str
	 * @return 验证通过返回true
	 */
	public static boolean isPhone(String str) {
		String reg="([0-9]{3,4}-)?[0-9]{7,8}";  
        return Pattern.matches(reg, str);  
	}
	
}
